from openpyxl import load_workbook
import pymysql

conn = pymysql.connect(
    host="43.156.247.50",  # 替换为实际的主机名
    user="root",  # 替换为实际的用户名
    password="520777",  # 替换为实际的密码
    database="house"  # 替换为实际的数据库名
)
cursor = conn.cursor()
        
# 加载 xlsx 文件
wb = load_workbook(filename='D:\house.xlsx')

# 选择要读取的工作表
sheet = wb.worksheets[0]

data = []
for row in sheet.iter_rows(values_only=True):
    data.append(row)

# 将数据每100条一批插入数据库
batch_size = 100
for i in range(0, len(data), batch_size):
    batch_data = data[i:i+batch_size]

    # 插入数据
    for row in batch_data:
        try:
            sql = 'INSERT INTO house.house_yy (`section`, block_no, unit_no, cell, price, unit_price, room, floor, tel, tel2, name, name2) VALUES '
            sql += '(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'
            
            # 执行 SQL 查询
            cursor.execute(sql, row)
        except Exception as e:
            print(e)
            
    conn.commit()

cursor.close()
conn.close()
